/**
 * www.easyplatform.cn ©2016
 */
package cn.easyplatform.studio.dao.impl;

import cn.easyplatform.entities.beans.table.TableBean;
import cn.easyplatform.entities.beans.table.TableField;
import cn.easyplatform.entities.beans.table.TableFk;
import cn.easyplatform.entities.beans.table.TableIndex;
import cn.easyplatform.lang.Lang;
import cn.easyplatform.lang.Nums;
import cn.easyplatform.lang.Strings;
import cn.easyplatform.studio.dao.*;
import cn.easyplatform.studio.dao.transaction.JdbcTransactions;
import cn.easyplatform.studio.vos.*;
import cn.easyplatform.type.FieldType;
import cn.hutool.core.text.StrBuilder;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

/**
 * @author <a href="mailto:shiny_vc@163.com">陈云亮</a> <br/>
 * @since 2.0.0 <br/>
 */
public abstract class AbstractBizDao implements BizDao {

    private final static Logger log = LoggerFactory.getLogger(AbstractBizDao.class);

    protected DataSource ds;

    public AbstractBizDao(DataSource dataSource) {
        this.ds = dataSource;
    }

    protected abstract Dialect getDialect();

    @Override
    public DataSource getDataSource() {
        return this.ds;
    }
    @Override
    public List<MenuVo> getMenus() {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            stmt = conn
                    .prepareStatement("select menuId,name,image,orderNo,parentMenuId,tasks,desp from sys_menu_info order by parentMenuId,orderNo");
            rs = stmt.executeQuery();
            List<MenuVo> data = new ArrayList<MenuVo>();
            while (rs.next()) {
                MenuVo mv = new MenuVo();
                mv.setId(rs.getString(1));
                mv.setName(rs.getString(2));
                mv.setImage(rs.getString(3));
                mv.setOrderNo(rs.getInt(4));
                mv.setParentMenuId(rs.getString(5));
                mv.setTasks(rs.getString(6));
                mv.setDesp(rs.getString(7));
                data.add(mv);
            }
            return data;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }
    @Override
    public List<AccessVo> getAccess() {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            stmt = conn
                    .prepareStatement("select code,`name`,controlType,remark from sys_access_info where 1=1");
            rs = stmt.executeQuery();
            List<AccessVo> data = new ArrayList<>();
            while (rs.next()) {
                AccessVo mv = new AccessVo();
                mv.setCode(rs.getString(1));
                mv.setName(rs.getString(2));
                mv.setRemark(rs.getString(4));
                mv.setControlType(rs.getString(3));
                data.add(mv);
            }
            return data;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }

    @Override
    public List<RoleAccessVo> getRoleAccess(String roleID) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            stmt = conn
                    .prepareStatement("select taskId,`type`,access from sys_role_access_info where roleID = '" + roleID + "'");
            rs = stmt.executeQuery();
            List<RoleAccessVo> data = new ArrayList<>();
            while (rs.next()) {
                RoleAccessVo mv = new RoleAccessVo();
                mv.setAccess(rs.getString(3));
                mv.setRoleId(roleID);
                mv.setTaskId(rs.getString(1));
                mv.setType(rs.getString(2));
                data.add(mv);
            }
            return data;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }
    @Override
    public List<Object[]> selectList(String sql, FieldVo... params) {
        return selectList(sql, null, params).getData();
    }

    @Override
    public ResultVo selectList(String sql, Page page, FieldVo... params) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            StringBuilder sb = new StringBuilder();
            if (page != null && page.isGetTotal()) {
                sb.append("select count(1) from (").append(sql).append(") c");
                pstmt = conn.prepareStatement(sb.toString());
                if (params != null && params.length > 0) {
                    for (int i = 0; i < params.length; i++)
                        DaoUtils.setValue(pstmt, i + 1, params[i]);
                }
                rs = pstmt.executeQuery();
                rs.next();
                page.setTotalCount(Nums.toInt(rs.getObject(1).toString(), 0));
                DaoUtils.closeQuietly(pstmt, rs);
                sb.setLength(0);
            }
            sb.append(sql);
            if (page != null && page.getOrderBy() != null)
                sb.append(" order by ").append(page.getOrderBy());
            if (page != null)
                sql = getDialect().getPageSql(sb.toString(), page);
            log.debug("getList->%s", sql);
            pstmt = conn.prepareStatement(sql);
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++)
                    DaoUtils.setValue(pstmt, i + 1, params[i]);
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            ResultVo vo = new ResultVo();
            List<Object[]> data = new ArrayList<Object[]>();
            int size = rsmd.getColumnCount();
            if (page != null && page.isGetTotal()) {
                List<String> columns = new ArrayList<String>();
                List<String> tableNames = new ArrayList<String>();
                for (int index = 1; index <= size; index++) {
                    if (rsmd.getColumnLabel(index) != null)
                        columns.add(rsmd.getColumnLabel(index));
                    else
                        columns.add(rsmd.getColumnName(index));
                    if (rsmd.getTableName(index)!=null)
                        tableNames.add(rsmd.getTableName(index));
                }
                vo.setColumns(columns);
                vo.setTableNames(tableNames);
            }
            while (rs.next()) {
                Object[] record = new Object[size];
                for (int index = 1; index <= size; index++)
                    record[index - 1] = DaoUtils.getValue(rs, rsmd, index);
                data.add(record);
            }
            vo.setData(data);
            return vo;
        } catch (SQLException ex) {
            log.error("selectList", ex);
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public int update(String sql, List<FieldVo> params) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            log.debug("update->%s,%s", sql, params);
            pstmt = conn.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++)
                    DaoUtils.setValue(pstmt, i + 1, params.get(i));
            }
            return pstmt.executeUpdate();
        } catch (SQLException ex) {
            log.error("update", ex);
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public List<TableBean> getTables() {
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            DatabaseMetaData dbmd = conn.getMetaData();
            int dbType = DaoUtils.getDbType(ds.toString());
            switch (dbType) {
                case DaoUtils.ORACLE:
                    rs = dbmd.getTables(null, dbmd.getUserName().toUpperCase(),
                            null, new String[]{"TABLE", "VIEW"});
                    break;
                case DaoUtils.SQLSERVER:
                    rs = dbmd.getTables(null, "dbo", null, new String[]{"TABLE",
                            "VIEW"});
                    break;
                default:
                    rs = dbmd.getTables(null, null, null, new String[]{"TABLE",
                            "VIEW"});
            }
            List<TableBean> tables = new ArrayList<TableBean>();
            while (rs.next()) {
                TableBean tb = revertTable(rs.getString("TABLE_NAME"));
                tables.add(tb);
            }
            return tables;
        } catch (SQLException ex) {
            log.error("getTables", ex);
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(rs);
        }
    }

    @Override
    public TableBean revertTable(String table) {
        Statement pstmt = null;
        ResultSet rs = null;
        try {
            log.debug("revertTable->%s", table);
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.createStatement();
            //备注
            int dbType = DaoUtils.getDbType(ds.toString());
            switch (dbType) {
                case DaoUtils.ORACLE:
                    rs = pstmt.executeQuery("select * from user_col_comments where Table_Name=" + table + " where 1=1");
                    break;
                case DaoUtils.MYSQL:
                    rs = pstmt.executeQuery("show full COLUMNS FROM " + table + " where 1=1");
                    break;
            }
            List<String> commentData = new ArrayList<>();
            while (rs.next()) {
                switch (dbType) {
                    case DaoUtils.ORACLE:
                        commentData.add(rs.getString(3));
                        break;
                    case DaoUtils.MYSQL:
                        commentData.add(rs.getString(9));
                        break;
                }

            }
            DaoUtils.closeQuietly(rs);
            // 栏位
            rs = pstmt.executeQuery("select * from " + table + " where 1<>1");
            ResultSetMetaData rsmd = rs.getMetaData();
            TableBean tb = new TableBean();
            tb.setId(table);
            List<TableField> fields = new ArrayList<TableField>();
            int count = rsmd.getColumnCount();
            for (int i = 1; i <= count; i++) {
                TableField tf = new TableField();
                if (rsmd.getColumnLabel(i) != null)
                    tf.setName(rsmd.getColumnLabel(i));
                else
                    tf.setName(rsmd.getColumnName(i));
                int decimal = rsmd.getScale(i);
                if (decimal > 0)
                    tf.setDecimal(decimal);
                tf.setLength(rsmd.getColumnDisplaySize(i));
                tf.setType(DaoUtils.getType(tf.getName(), rsmd.getColumnType(i)));
                tf.setDescription(commentData.get(i - 1));
                fields.add(tf);
            }
            tb.setFields(fields);
            DaoUtils.closeQuietly(rs);
            String scheme = null;
            if (DaoUtils.getDbType(ds.toString()) == DaoUtils.ORACLE) {
                scheme = ((DruidDataSource) ds).getUsername().toUpperCase();
                table = table.toUpperCase();
            }
            // 主KEY
            DatabaseMetaData dbmd = conn.getMetaData();
            rs = dbmd.getPrimaryKeys(null, scheme, table);
            List<String> keys = new ArrayList<String>();
            //AUTO_INCREMENT
            while (rs.next()) {
                keys.add(rs.getString("COLUMN_NAME"));
                /*if (rs.getBoolean("AUTO_INCREMENT") == true && tb.isByDb() == false)
                    tb.setByDb(true);*/
            }
            tb.setKey(keys);
            DaoUtils.closeQuietly(rs);
            //自增长
            rs = dbmd.getColumns(null, "%", table,"%");
            //AUTO_INCREMENT
            while (rs.next()) {
                if ("YES".equals(rs.getString("IS_AUTOINCREMENT"))) {
                    tb.setByDb(true);
                    tb.setAutoKey(true);
                }
            }
            DaoUtils.closeQuietly(rs);
            // 索引
            rs = dbmd.getIndexInfo(null, scheme, table, false, true);
            List<TableIndex> indexes = new ArrayList<TableIndex>();
            Map<String, TableIndex> tmp = new HashMap<String, TableIndex>();
            while (rs.next()) {
                String name = rs.getString("INDEX_NAME");
                if (Strings.isBlank(name)
                        || name.toUpperCase().equals("PRIMARY"))
                    continue;
                TableIndex index = tmp.get(name);
                if (index == null) {
                    index = new TableIndex();
                    index.setName(name);
                    tmp.put(name, index);
                    indexes.add(index);
                }
                index.setFields((index.getFields() == null ? "" : index
                        .getFields())
                        + rs.getString("COLUMN_NAME"));
                index.setFields(index.getFields() + ",");
                index.setUnique(!rs.getBoolean("NON_UNIQUE"));
            }
            Iterator<TableIndex> itr = indexes.iterator();
            while (itr.hasNext()) {
                TableIndex index = itr.next();
                if (index.getFields().endsWith(","))
                    index.setFields(index.getFields().substring(0,
                            index.getFields().lastIndexOf(",")));
                boolean match = false;
                for (String s : keys) {
                    for (String t : index.getFields().split(",")) {
                        if (t.equals(s)) {
                            match = true;
                            break;
                        }
                    }
                    if (!match)
                        break;
                }
                /*if (match)// 是主KEY生成的索引
                    itr.remove();*/
            }
            tb.setIndexes(indexes);
            DaoUtils.closeQuietly(rs);
            // 外键
            rs = dbmd.getImportedKeys(null, scheme, table);
            List<TableFk> fks = new ArrayList<TableFk>();
            while (rs.next()) {
                TableFk fk = new TableFk();
                fk.setName(rs.getString("FK_NAME"));
                fk.setToFields(rs.getString("PKCOLUMN_NAME"));
                fk.setReferences(rs.getString("PKTABLE_NAME"));
                fk.setSourceFields(rs.getString("FKCOLUMN_NAME"));
                fks.add(fk);
            }
            tb.setForeignKeys(fks);
            DaoUtils.closeQuietly(rs);
            return tb;
        } catch (SQLException ex) {
            throw new DaoException(table + ":" + ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(rs);
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createTable(TableBean table) {
        Statement stmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            StringBuilder sb = new StringBuilder();
            if (Strings.isBlank(table.getView())) {
                sb.append("CREATE TABLE ").append(table.getId()).append(" (");
                for (TableField tf : table.getFields()) {
                    sb.append(tf.getName());
                    sb.append(' ').append(evalFieldType(tf));
                    if (tf.isNotNull())
                        sb.append(" NOT NULL");
                    else
                        sb.append(" NULL");
                    if (DaoUtils.MYSQL == DaoUtils.getDbType(ds.toString())){
                        if (table.isByDb()){//添加由数据表自增的语句
                            if (table.getKey().get(0).equals(tf.getName())){
                                sb.append(" AUTO_INCREMENT");
                            }
                        }
                        sb.append(" comment'").append(tf.getDescription()).append("'");
                    }
                    sb.append(',');
                }
                if (table.getKey() != null && !table.getKey().isEmpty()) {
                    sb.append(" PRIMARY KEY (");
                    for (String field : table.getKey()) {
                        sb.append(field).append(",");
                    }
                    sb.setCharAt(sb.length() - 1, ')');
                } else
                    sb.deleteCharAt(sb.length() - 1);
                sb.append(")");
            } else
                sb.append(table.getView().replaceAll("\\s", " "));
            log.debug(sb.toString());
            System.out.println("create:" + sb.toString());
            stmt = conn.createStatement();
            stmt.execute(sb.toString());
            stmt.close();
            if (Strings.isBlank(table.getView())) {
                if (DaoUtils.ORACLE == DaoUtils.getDbType(ds.toString())) {
                    sb.setLength(0);
                    for (TableField tf : table.getFields()) {
                        if (Strings.isBlank(tf.getDescription()))
                            sb.append("comment on column ").append(table.getId()).append(".").append(tf.getName())
                                    .append(" is '").append(tf.getDescription()).append("';");
                    }

                    log.debug(sb.toString());
                    if (sb.length() > 0) {
                        stmt = conn.createStatement();
                        stmt.execute(sb.toString());
                        stmt.close();
                    }
                    if (table.isByDb()){//添加用于数据表自增的语句
                        //添加用于数据表自增的序列
                        StringBuffer seqBuffer = new StringBuffer();
                        seqBuffer.append("CREATE SEQUENCE "+table.getId()+"_SEQ"+"\n");
                        seqBuffer.append("INCREMENT BY 1"+"\n");
                        seqBuffer.append("START WITH 1"+"\n");
                        seqBuffer.append("NOMAXVALUE"+"\n");
                        seqBuffer.append("NOCYCLE"+"\n");
                        seqBuffer.append("NOCACHE");
                        stmt = conn.createStatement();
                        stmt.execute(seqBuffer.toString());
                        stmt.close();
                        //添加用于数据表自增的触发器
                        StringBuffer trgBuffer = new StringBuffer();
                        trgBuffer.append("CREATE OR REPLACE TRIGGER "+table.getId()+"_TRG"+"\n");
                        trgBuffer.append("BEFORE INSERT ON "+table.getId()+"\n");
                        trgBuffer.append("FOR EACH ROW"+"\n");
                        trgBuffer.append("BEGIN"+"\n");
                        trgBuffer.append("SELECT "+table.getId()+"_SEQ"+".NEXTVAL INTO :NEW.ID FROM DUAL;"+"\n");
                        trgBuffer.append("END;");
                        stmt = conn.createStatement();
                        stmt.execute(seqBuffer.toString());
                        stmt.close();
                    }
                }
            }
            if (Strings.isBlank(table.getView())) {
                if (table.getIndexes() != null && !table.getIndexes().isEmpty()) {
                    for (TableIndex index : table.getIndexes()) {
                        if (!Strings.isBlank(index.getName())
                                && !Strings.isBlank(index.getFields())) {
                            sb.setLength(0);
                            if (index.isUnique())
                                sb.append("Create UNIQUE Index ");
                            else
                                sb.append("Create Index ");
                            sb.append(index.getName());
                            sb.append(" ON ").append(table.getId()).append("(")
                                    .append(index.getFields()).append(")");
                            log.debug(sb.toString());
                            stmt = conn.createStatement();
                            stmt.execute(sb.toString());
                            stmt.close();
                        }
                    }
                }
                if (table.getForeignKeys() != null
                        && !table.getForeignKeys().isEmpty()) {
                    for (TableFk fk : table.getForeignKeys()) {
                        if (!Strings.isBlank(fk.getName())
                                && !Strings.isBlank(fk.getToFields())
                                && !Strings.isBlank(fk.getReferences())
                                && !Strings.isBlank(fk.getSourceFields())) {
                            sb.setLength(0);
                            sb.append("ALTER TABLE ").append(table.getId())
                                    .append(" ADD FOREIGN KEY (")
                                    .append(fk.getSourceFields())
                                    .append(") REFERENCES ")
                                    .append(fk.getReferences()).append("(")
                                    .append(fk.getToFields()).append(")");
                            log.debug(sb.toString());
                            stmt = conn.createStatement();
                            stmt.execute(sb.toString());
                            stmt.close();
                        }
                    }
                }
            }
        } catch (SQLException ex) {
            log.error("createTable", ex);
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt);
        }
    }

    @Override
    public void buildForeignKey(TableBean source, TableBean target) {
        Statement stmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            // 先删除旧的外键
            if (target.getForeignKeys() != null
                    && !target.getForeignKeys().isEmpty()) {
                stmt = conn.createStatement();
                for (TableFk fk : target.getForeignKeys())
                    stmt.addBatch(dropForeignKey(source.getId(), fk.getName()));
                stmt.executeBatch();
                stmt.close();
                stmt = null;
            }
            if (source.getForeignKeys() != null
                    && !source.getForeignKeys().isEmpty()) {
                stmt = conn.createStatement();
                StringBuilder sb = new StringBuilder();
                for (TableFk fk : source.getForeignKeys()) {
                    sb.setLength(0);
                    sb.append("ALTER TABLE ").append(source.getId())
                            .append(" ADD CONSTRAINT").append(fk.getName())
                            .append(" FOREIGN KEY (")
                            .append(fk.getSourceFields()).append(")")
                            .append(fk.getReferences()).append("(")
                            .append(fk.getToFields()).append(")");
                    log.debug(sb.toString());
                    stmt.addBatch(sb.toString());
                }
                stmt.executeBatch();
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt);
        }
    }

    @Override
    public void buildIndex(TableBean source, TableBean target) {
        Statement stmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            // 先删除旧的索引
            if (target.getIndexes() != null && !target.getIndexes().isEmpty()) {
                stmt = conn.createStatement();
                for (TableIndex idx : target.getIndexes())
                    stmt.addBatch(dropIndex(source.getId(), idx.getName()));
                stmt.executeBatch();
                stmt.close();
                stmt = null;
            }
            if (source.getIndexes() != null && !source.getIndexes().isEmpty()) {
                stmt = conn.createStatement();
                StringBuilder sb = new StringBuilder();
                for (TableIndex idx : source.getIndexes()) {
                    sb.setLength(0);
                    if (idx.isUnique())
                        sb.append("Create UNIQUE Index ");
                    else
                        sb.append("Create Index ");
                    sb.append(idx.getName());
                    sb.append(" ON ").append(source.getId()).append("(")
                            .append(idx.getFields()).append(")");
                    log.debug(sb.toString());
                    stmt.addBatch(sb.toString());
                }
                stmt.executeBatch();
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt);
        }
    }

    @Override
    public void buildTable(TableVo tv) {
        Statement stmt = null;
        String tmpTable1 = null;// 原有的旧表备份
        //String tmpTable2 = null;// 根据参数表备份数据
        Connection conn = null;
        boolean droped = false;
        try {
            conn = JdbcTransactions.getConnection(ds);
            TableBean source = tv.getSource();
            StringBuilder sb = new StringBuilder();
            if (tv.getDiffFields() == null || tv.getDiffFields().isEmpty()) {// 仅添加或删除栏位
                if (tv.getTargetNewFields() != null
                        && !tv.getTargetNewFields().isEmpty()) {// 先清掉真实表多余的栏位
                    stmt = conn.createStatement();
                    for (String field : tv.getTargetNewFields()) {
                        sb.append("ALTER TABLE ").append(source.getId())
                                .append(" DROP ").append(getDialect().evalField(field));
                        stmt.addBatch(sb.toString());
                        sb.setLength(0);
                    }
                    stmt.executeBatch();
                    stmt.close();
                    stmt = null;
                }
                if (tv.getSourceNeFields() != null
                        && !tv.getSourceNeFields().isEmpty()) {// 添加栏位
                    stmt = conn.createStatement();
                    for (String field : tv.getSourceNeFields()) {
                        for (TableField tf : source.getFields()) {
                            if (tf.getName().equalsIgnoreCase(field)) {
                                String fieldName = "";
                                switch (DaoUtils.getDbType(ds.toString())) {
                                    case DaoUtils.ORACLE:
                                        fieldName = field;
                                        break;
                                    case DaoUtils.DB2:
                                    case DaoUtils.MYSQL:
                                    case DaoUtils.SQLSERVER:
                                    case DaoUtils.PSQL:
                                    case DaoUtils.DERBY:
                                    case DaoUtils.SYBASE:
                                        fieldName = tf.getName();
                                        break;
                                }
                                sb.append("ALTER TABLE ")
                                        .append(source.getId())
                                        .append(" ADD ").append(getDialect().evalField(fieldName))
                                        .append(" ")
                                        .append(getDialect().evalFieldType(tf));
                                if (tf.isNotNull())
                                    sb.append(" NOT NULL");
                                stmt.addBatch(sb.toString());
                                sb.setLength(0);
                                break;
                            }
                        }
                    }
                    stmt.executeBatch();
                    stmt.close();
                    stmt = null;
                }
            } else {
                sb.append("SELECT ");
                StringBuilder insertSQL = new StringBuilder();
                insertSQL.append("INSERT INTO ").append(source.getId())
                        .append("(");
                int count = 0;
                List<TableField> fields = new ArrayList<TableField>();
                for (TableField field : source.getFields()) {
                    if (tv.getSourceNeFields() != null// 排除那些增加的栏位
                            && !tv.getSourceNeFields().isEmpty()) {
                        boolean f = false;
                        for (String n : tv.getSourceNeFields()) {
                            if (n.equalsIgnoreCase(field.getName())) {
                                f = true;
                                break;
                            }
                        }
                        if (f)
                            continue;
                    }
                    sb.append(field.getName()).append(",");
                    insertSQL.append(field.getName()).append(",");
                    fields.add(field);
                    count++;
                }
                sb.deleteCharAt(sb.length() - 1);
                String sql = sb.toString();
                // 先备份原有的数据到临时表
                tmpTable1 = backup(source.getId());
                // 再按新格式备份数据到另外一个临时表
                //tmpTable2 = backup(source.getId(), fields);
                // 删除原有的表
                sb.setLength(0);
                sb.append("DROP TABLE ").append(source.getId());
                stmt = conn.createStatement();
                System.out.println("DROP:" + sb.toString());
                stmt.execute(sb.toString());
                stmt.close();
                stmt = null;
                droped = true;
                // 创建新的表
                createTable(source);
                droped = false;
                // 从临时表取数据到新增的表
                sb.setLength(0);
                sb.append(sql).append(" FROM ").append(tmpTable1);
                insertSQL.deleteCharAt(insertSQL.length() - 1);
                insertSQL.append(") VALUES(")
                        .append(StringUtils.repeat("?", ",", count))
                        .append(")");
                log.debug(sb.toString());
                log.debug(insertSQL.toString());
                stmt = conn.createStatement();
                PreparedStatement pstmt = conn.prepareStatement(insertSQL
                        .toString());
                System.out.println("select:" + sb.toString());
                System.out.println("insert:" + insertSQL.toString());
                ResultSet rs = stmt.executeQuery(sb.toString());
                while (rs.next()) {
                    for (int i = 0; i < count; i++) {
                        Object val = rs.getObject(i + 1);
                        try {
                            val = FieldType.cast(val, fields.get(i).getType());
                        } catch (Exception ex) {
                            val = null;
                        }
                        pstmt.setObject(i + 1, val);
                    }
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                pstmt.close();
                rs.close();
                stmt.close();
            }
        } catch (Exception ex) {
            log.error("buildTable", ex);
            // 如果有异常且表已被删除，恢复旧数据:事务失效？？不能自动恢复原有的表
            if (droped)
                restore(tmpTable1, tv.getSource().getId());
            if (ex instanceof DaoException)
                throw (DaoException) ex;
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt);
            if (tmpTable1 != null) {// 必须清除临时表
                try {
                    stmt = conn.createStatement();
                    stmt.execute("DROP TABLE " + tmpTable1);
                } catch (Exception ex) {
                } finally {
                    DaoUtils.closeQuietly(stmt);
                }
            }
            /*if (tmpTable2 != null) {// 必须清除临时表
                try {
                    stmt = conn.createStatement();
                    stmt.execute("DROP TABLE " + tmpTable2);
                } catch (Exception ex) {
                } finally {
                    DaoUtils.closeQuietly(stmt);
                }
            }*/
        }
    }

    protected void restore(String source, String target) {
        Statement stmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT * INTO ").append(target).append(" FROM ")
                    .append(source);
            stmt = conn.createStatement();
            stmt.execute(sb.toString());
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt);
        }
    }

    protected String backup(String table/*, List<TableField> fields*/) {
        Statement stmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            StringBuilder sb = new StringBuilder();
            /*sb.append("SELECT ");
            for (TableField tf : fields)
                sb.append(tf.getName()).append(",");
            sb.deleteCharAt(sb.length() - 1);
            String tmpTable = table + "_"
                    + RandomStringUtils.randomAlphanumeric(10);
            sb.append(" INTO ").append(tmpTable).append(" FROM ").append(table);*/
            String tmpTable = table + "_"
                    + RandomStringUtils.randomAlphanumeric(10);
            sb.append("CREATE TABLE ").append(tmpTable).append(" LIKE ").append(table);
            stmt = conn.createStatement();
            System.out.println("backup:" + sb.toString());
            stmt.execute(sb.toString());
            return tmpTable;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt);
        }
    }

    @Override
    public void buildPrimaryKey(TableBean source, TableBean target) {
        Statement stmt = null;
        try {
            StringBuilder sb = new StringBuilder();
            Connection conn = JdbcTransactions.getConnection(ds);
            if (target.getKey() != null && !target.getKey().isEmpty()) {
                sb.append("ALTER TABLE ").append(source.getId())
                        .append(" DROP PRIMARY KEY");
                stmt = conn.createStatement();
                stmt.execute(sb.toString());
                stmt.close();
                stmt = null;
                sb.setLength(0);
            }
            if (source.getKey() != null && !source.getKey().isEmpty()) {
                sb.append("ALTER TABLE ").append(source.getId())
                        .append(" ADD PRIMARY KEY (");
                Iterator<String> itr = source.getKey().iterator();
                while (itr.hasNext()) {
                    sb.append(itr.next());
                    if (itr.hasNext())
                        sb.append(",");
                }
                sb.append(")");
                stmt = conn.createStatement();
                stmt.execute(sb.toString());
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt);
        }
    }

    @Override
    public void dropTable(TableBean table) {
        Statement stmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            stmt = conn.createStatement();
            String sql = "DROP" + (Strings.isBlank(table.getView()) ? " TABLE " : " VIEW ") + table.getId();
            log.debug(sql);
            stmt.execute(sql);
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt);
        }
    }

    @Override
    public List<String> getColumnNameList(String table) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            //AND Table_schema='cj_data'
            pstmt = conn
                    .prepareStatement("select COLUMN_NAME from information_schema.COLUMNS where table_name = ? and Table_schema = ?");
            pstmt.setString(1, table);
            pstmt.setString(2, conn.getCatalog());
            List<String> columnNameList = new ArrayList<>();
            rs = pstmt.executeQuery();
            while (rs.next()) {
                columnNameList.add(rs.getString(1));
            }
            return columnNameList;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public boolean exists(String table) {
        Statement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.createStatement();
            rs = pstmt.executeQuery("select count(*) from " + table
                    + " where 1<>1");
            if (rs.next())
                return true;
            return false;
        } catch (SQLException ex) {
            return false;
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    protected String dropForeignKey(String table, String name) {
        StringBuilder sb = new StringBuilder();
        sb.append("ALTER TABLE ").append(table).append(" DROP CONSTRAINT ")
                .append(name);
        return sb.toString();
    }

    protected String dropIndex(String table, String name) {
        StringBuilder sb = new StringBuilder();
        sb.append("DROP INDEX ").append(name);
        return sb.toString();
    }

    protected String evalFieldType(TableField field) {
        switch (field.getType()) {
            case CHAR:
                return "CHAR(" + field.getLength() + ")";

            case BOOLEAN:
                return "INT";

            case VARCHAR:
                return "VARCHAR(" + field.getLength() + ")";

            case CLOB:
                return "TEXT";

            case OBJECT:
            case BLOB:
                return "BLOB";

            case DATETIME:
                return "DATETIME";

            case DATE:
                return "DATE";
            case TIME:
                return "TIME";

            case INT:
                // 用户自定义了宽度
                if (field.getLength() > 0)
                    return "INT(" + field.getLength() + ")";
                // 用数据库的默认宽度
                return "INT";

            case NUMERIC:
                // 用户自定义了精度
                if (field.getLength() > 0) {
                    return "NUMERIC(" + field.getLength() + ","
                            + field.getDecimal() + ")";
                } else
                    // 用默认精度
                    return "NUMERIC(23,2)";
            default:
                throw Lang.makeThrow("Unsupport colType '%s' of field '%s'",
                        field.getType(), field.getName());
        }
    }
}
